
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 01/05/2012 14:59:29
-- Generated from EDMX file: C:\Users\wei\Documents\Visual Studio 2010\Projects\romatimesystem\RomaBackend\romaModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO

Use [master];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_TimeReport_Project1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TimeReport] DROP CONSTRAINT [FK_TimeReport_Project1];
GO
IF OBJECT_ID(N'[dbo].[FK_UserRole_Project]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserRole] DROP CONSTRAINT [FK_UserRole_Project];
GO
IF OBJECT_ID(N'[dbo].[FK_TimeReport_User1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[TimeReport] DROP CONSTRAINT [FK_TimeReport_User1];
GO
IF OBJECT_ID(N'[dbo].[FK_UserRole_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserRole] DROP CONSTRAINT [FK_UserRole_User];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Project]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Project];
GO
IF OBJECT_ID(N'[dbo].[TimeReport]', 'U') IS NOT NULL
    DROP TABLE [dbo].[TimeReport];
GO
IF OBJECT_ID(N'[dbo].[User]', 'U') IS NOT NULL
    DROP TABLE [dbo].[User];
GO
IF OBJECT_ID(N'[dbo].[UserRole]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserRole];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Project'
CREATE TABLE [dbo].[Project] (
    [id] int IDENTITY(1,1) NOT NULL,
    [name] varchar(50)  NOT NULL,
    [description] varchar(max)  NOT NULL
);
GO

-- Creating table 'TimeReport'
CREATE TABLE [dbo].[TimeReport] (
    [id] int IDENTITY(1,1) NOT NULL,
    [user_id] int  NOT NULL,
    [project_id] int  NOT NULL,
    [start] datetime  NOT NULL,
    [stop] datetime  NOT NULL,
    [description] varchar(max)  NOT NULL
);
GO

-- Creating table 'User'
CREATE TABLE [dbo].[User] (
    [id] int IDENTITY(1,1) NOT NULL,
    [name] varchar(50)  NOT NULL,
    [password] varchar(50)  NOT NULL,
    [role] int  NOT NULL,
    [username] varchar(50)  NOT NULL
);
GO

-- Creating table 'UserRole'
CREATE TABLE [dbo].[UserRole] (
    [id] int IDENTITY(1,1) NOT NULL,
    [role] int  NOT NULL,
    [user_id] int  NOT NULL,
    [project_id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [id] in table 'Project'
ALTER TABLE [dbo].[Project]
ADD CONSTRAINT [PK_Project]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'TimeReport'
ALTER TABLE [dbo].[TimeReport]
ADD CONSTRAINT [PK_TimeReport]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'User'
ALTER TABLE [dbo].[User]
ADD CONSTRAINT [PK_User]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- Creating primary key on [id] in table 'UserRole'
ALTER TABLE [dbo].[UserRole]
ADD CONSTRAINT [PK_UserRole]
    PRIMARY KEY CLUSTERED ([id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [project_id] in table 'TimeReport'
ALTER TABLE [dbo].[TimeReport]
ADD CONSTRAINT [FK_TimeReport_Project1]
    FOREIGN KEY ([project_id])
    REFERENCES [dbo].[Project]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TimeReport_Project1'
CREATE INDEX [IX_FK_TimeReport_Project1]
ON [dbo].[TimeReport]
    ([project_id]);
GO

-- Creating foreign key on [project_id] in table 'UserRole'
ALTER TABLE [dbo].[UserRole]
ADD CONSTRAINT [FK_UserRole_Project]
    FOREIGN KEY ([project_id])
    REFERENCES [dbo].[Project]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserRole_Project'
CREATE INDEX [IX_FK_UserRole_Project]
ON [dbo].[UserRole]
    ([project_id]);
GO

-- Creating foreign key on [user_id] in table 'TimeReport'
ALTER TABLE [dbo].[TimeReport]
ADD CONSTRAINT [FK_TimeReport_User1]
    FOREIGN KEY ([user_id])
    REFERENCES [dbo].[User]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TimeReport_User1'
CREATE INDEX [IX_FK_TimeReport_User1]
ON [dbo].[TimeReport]
    ([user_id]);
GO

-- Creating foreign key on [user_id] in table 'UserRole'
ALTER TABLE [dbo].[UserRole]
ADD CONSTRAINT [FK_UserRole_User]
    FOREIGN KEY ([user_id])
    REFERENCES [dbo].[User]
        ([id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserRole_User'
CREATE INDEX [IX_FK_UserRole_User]
ON [dbo].[UserRole]
    ([user_id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------